<?php

    class Application_Model_Custom extends Zend_Db_Table_Abstract
    {

        protected $_name = "users";

        function init()
        {
            
        }

        public function getUserEventComments($whereCond, $order = NULL, $limit = NULL)
        {
            $records = $this->getAdapter()->select()
                    ->from(array("ecm" => TABLE_EVENT_COMMENTS))
                    ->join(array("e" => TABLE_EVENTS), "e.event_id=ecm.ecm_event_id")
                    ->join(array("c" => TABLE_CATEGORY), "c.category_id=e.event_cat_id")
                    ->join(array("sc" => TABLE_SUB_CATEGORY), "sc.subcategory_id=e.event_subcat_id")
                    ->where($whereCond)
            ;

            if ($order != NULL)
                $records = $records->order($order);

            if ($limit != NULL)
                $records = $records->limit($limit);

//            prd($records->__toString());

            $records = $records->query()->fetchAll();
//            prd($records);

            return $records;
        }

        public function getUserNotifications($user_id, $limit = false, $notification_count = false, $pagination = false)
        {
            $subQuery = $this->getAdapter()->select()
                    ->from(TABLE_EVENT_JOIN)
                    ->where('ej_is_accepted = "1"')
                    ->where('ej_user_id = "' . $user_id . '"')
                    ->group('ej_event_id')
                    ->group('ej_user_id');

            $records = $this->getAdapter()->select()
                    ->from(TABLE_NOTIFICATION)
                    ->join(TABLE_STATUS_CODE, TABLE_NOTIFICATION . ".notification_code = " . TABLE_STATUS_CODE . ".status_code")
                    ->join(TABLE_EVENTS, TABLE_EVENTS . ".event_id = " . TABLE_NOTIFICATION . ".notification_event_id")
                    ->joinLeft(TABLE_USERS, TABLE_USERS . ".user_id = " . TABLE_NOTIFICATION . ".notification_event_joiner")
                    ->joinLeft(array('ej' => $subQuery), "ej.ej_event_id = " . TABLE_EVENTS . ".event_id and ej_is_accepted= '1' ")
                    ->where("notification_user_id = " . $user_id . " and notification_status = '1'");


            if ($notification_count)
                $records = $records->where("notification_count = 'visible' ");

            //prd($records->__toString());

            $records = $records
                    ->order("notification_timestamp desc")
            ;

            if ($limit)
                $records = $records->limit($limit);

            if ($pagination)
                return $records;

            return $records->query()->fetchAll();
        }

        public function getUserEventsCounts($user_id)
        {
            $output = array();

            $published_records = $this->getAdapter()->select()
                            ->from(TABLE_EVENTS, new Zend_Db_Expr("COUNT(event_id) as publishedEvents"))
                            ->where("event_created_by = $user_id")
                            ->query()->fetch();

            $private_records = $this->getAdapter()->select()
                            ->from(TABLE_EVENTS, new Zend_Db_Expr("COUNT(event_id) as privateEvents"))
                            ->where("event_created_by = $user_id AND event_type = 'private'")
                            ->query()->fetch();

            $public_records = $this->getAdapter()->select()
                            ->from(TABLE_EVENTS, new Zend_Db_Expr("COUNT(event_id) as publicEvents"))
                            ->where("event_created_by = $user_id AND event_type = 'public'")
                            ->query()->fetch();

            $attended_records = $this->getAdapter()->select()
                            ->from(TABLE_EVENT_JOIN, new Zend_Db_Expr("COUNT(ej_id) as attendedEvents"))
                            ->where("ej_user_id = $user_id AND ej_is_accepted = '1'")
                            ->query()->fetch();

            $output["published"] = $published_records["publishedEvents"];
            $output["private"] = $private_records["privateEvents"];
            $output["public"] = $public_records["publicEvents"];
            $output["attended"] = $attended_records["attendedEvents"];

            return $output;
        }

        public function getAllEventDetails($whereCond = "1", $order = NULL, $limit = NULL, $paging = NULL)
        {
            if ($whereCond == "1" || empty($whereCond))
                $whereCond = "event_active = '1' AND event_ended = '0'";

            $records = $this->getAdapter()->select()
                    ->from(array("e" => TABLE_EVENTS))
                    ->joinLeft(array("c" => TABLE_CATEGORY), "c.category_id=e.event_cat_id")
                    ->joinLeft(array("sc" => TABLE_SUB_CATEGORY), "sc.subcategory_id=e.event_subcat_id")
                    ->joinLeft(array("ct" => TABLE_COUNTRY), "ct.country_id=e.event_country")
                    ->joinLeft(array("u" => TABLE_USERS), "u.user_id=e.event_created_by")
                    ->where($whereCond)
            ;

            if ($order != NULL)
                $records = $records->order($order);

            if ($limit != NULL)
                $records = $records->limit($limit);

//            prd($records->__toString());
            if ($paging)
                return $records;

            $records = $records->query()->fetchAll();
            return $records;
        }

        public function getRating($user_id, $max_rating = RATING_STARS_MAX)
        {
            $record = $this->getAdapter()->select()
                    ->from(array("ur" => TABLE_RATING), new Zend_Db_Expr("SUM(rating) as totalrating, COUNT(rating) as totalcount"))
                    ->where("user_rate_to = $user_id");
            $record = $record->query()->fetch();

            if ($record["totalrating"] == "0" || empty($record["totalrating"]))
                $totalrating = "0";
            else
                $totalrating = trim($record["totalrating"] / $record["totalcount"]);

            $output = array(
                "totalvotes" => $record["totalcount"],
                "totalrating" => $totalrating,
            );
            return $output;
        }

        public function getFriendsAndGroups($user_id, $orderGroup = NULL, $limitGroup = NULL, $orderFriend = NULL, $limitFriend = NULL)
        {
            $group_records = $this->getAdapter()->select()
                    ->from(array("fg" => TABLE_FRIEND_GROUPS), array("group_id", "group_name"))
                    ->where("fg.created_by = $user_id");

            //prd($group_records->__toString());
            if ($orderGroup != NULL)
                $group_records = $group_records->order($orderGroup);

            if ($limitGroup != NULL)
                $group_records = $group_records->limit($limitGroup);

            $group_records = $group_records->query()->fetchAll();

            foreach ($group_records as $g_key => $g_value)
            {
                $group_array = array();
                $friends_records = $this->getAdapter()->select()->from(array("fr" => TABLE_FRIEND_REQUESTS))
                        ->joinLeft(array("u" => TABLE_USERS), "u.user_id=fr.user_sent_to")
                        ->where("fr.user_sent_from=$user_id and fr.group_id = " . $g_value["group_id"] . " and u.user_active='1'");

                if ($orderFriend != NULL)
                    $friends_records = $friends_records->order($orderFriend);

                if ($limitFriend != NULL)
                    $friends_records = $friends_records->limit($limitFriend);

                $friends_records = $friends_records->query()->fetchAll();

                $group_array = array(
                    "group_id" => $g_value["group_id"],
                    "group_name" => $g_value["group_name"],
                    "friends_records" => $friends_records
                );
                $output[] = $group_array;
            }

            $other_group_friends = $this->getAdapter()->select()->from(array("fr" => TABLE_FRIEND_REQUESTS))
                    ->joinLeft(array("u" => TABLE_USERS), "u.user_id=fr.user_sent_to")
                    ->where("fr.user_sent_from=$user_id and fr.group_id = '0'");

            if ($orderFriend != NULL)
                $other_group_friends = $other_group_friends->order($orderFriend);

            if ($limitFriend != NULL)
                $other_group_friends = $other_group_friends->limit($limitFriend);
            $other_group_friends = $other_group_friends->query()->fetchAll();

            $other_group_array = array(
                "group_id" => "others",
                "group_name" => "Others",
                "friends_records" => $other_group_friends
            );
            $output[] = $other_group_array;

            return $output;
        }

        public function getAllProfileCommentsFromAndTo($whereCond = "1")
        {
            $from_records = $this->getAdapter()->select()
                    ->from(array("ur" => TABLE_RATING))
                    ->join(array("u" => TABLE_USERS), "u.user_id=ur.user_rate_from")
                    ->where($whereCond);

            $from_records = $from_records->query()->fetchAll();

            $to_records = $this->getAdapter()->select()
                    ->from(array("ur" => TABLE_RATING))
                    ->join(array("u" => TABLE_USERS), "u.user_id=ur.user_rate_to")
                    ->where($whereCond);

            $to_records = $to_records->query()->fetchAll();

            $data_array = array(
                "from_records" => $from_records,
                "to_records" => $to_records
            );

            return $data_array;
        }

        public function getMyReviewsList($user_id, $orderType = "DESC", $limit = NULL, $debug = false)
        {
            $records = $this->getAdapter()->select()->from(array("ecm" => TABLE_EVENT_COMMENTS))
                    ->joinLeft(array("e" => TABLE_EVENTS), "e.event_id = ecm.ecm_event_id")
//                    ->joinLeft(array("u" => TABLE_USERS), "u.user_id = e.event_created_by")
                    ->joinLeft(array("u" => TABLE_USERS), "u.user_id = ecm.ecm_user_id")
                    ->where("e.event_created_by = $user_id")
                    ->order("ecm.ecm_id $orderType");

            if ($limit != NULL)
            {
                $records = $records->limit($limit);
            }

            if ($debug == TRUE)
            {
                prd($records->__toString());
            }

            $records = $records->query()->fetchAll();
//            prd($records);

            return $records;
        }

        public function getMyRatesList($user_id, $orderType = "ASC", $limit = NULL, $debug = false)
        {
            $records = $this->getAdapter()->select()->from(array("ej" => TABLE_EVENT_JOIN))
                    ->joinLeft(array("e" => TABLE_EVENTS), "e.event_id = ej.ej_event_id")
//                    ->joinLeft(array("ecm" => TABLE_EVENT_COMMENTS), "e.event_id = ecm.ecm_event_id")
                    ->joinLeft(array("c" => TABLE_CATEGORY), "c.category_id=e.event_cat_id")
                    ->joinLeft(array("sc" => TABLE_SUB_CATEGORY), "sc.subcategory_id=e.event_subcat_id")
                    ->where(" ej.ej_is_invited = '0' AND ej.ej_is_accepted = '1' AND ej.ej_user_id = $user_id")
//                    ->order("ecm.ecm_id $orderType")
            ;

            if ($limit != NULL)
            {
                $records = $records->limit($limit);
            }

            if ($debug == TRUE)
            {
                prd($records->__toString());
            }

            $records = $records->query()->fetchAll();
//            prd($records);

            foreach ($records as $key => $value)
            {
                $event_id = $value["ej_event_id"];
                $comment_records = $this->getAdapter()->select()->from(array("ecm" => TABLE_EVENT_COMMENTS))->where("ecm_event_id = $event_id AND ecm_user_id = $user_id")->order("ecm_id $orderType");
//                prd($comment_records->__toString());
                $comment_records = $comment_records->query()->fetchAll();
//                prd($comment_records);
                $records[$key]["comment_records"] = $comment_records;
            }
//            prd($records);

            return $records;
        }

        public function getEventAverageRating($user_id)
        {
            $record = $this->getAdapter()->select()
                    ->from(array("ecm"=>TABLE_EVENT_COMMENTS), array(new Zend_Db_Expr("SUM(ecm_ratings) as totalsum, COUNT(ecm_id) as totalcount")))
                    ->join(array("e"=>TABLE_EVENTS),"ecm.ecm_event_id = e.event_id")
                    ->where("e.event_created_by = $user_id")
            ;
            $record = $record->query()->fetch();

            $totalsum = $record["totalsum"];
            $totalcount = $record["totalcount"];
            $output = $totalsum / $totalcount;
            return $output;
        }

    }